from sqlalchemy import Column,String,Integer
from sqlalchemy.orm import aliased

from db_util import Base,Session,engine

class City(Base):
    __tablename__='t_city'
    id=Column(Integer,primary_key=True,autoincrement=True)
    name = Column(String(32))
    pid = Column(Integer)

    def __repr__(self):
        return f'<city:id={self.id} name={self.name} pid={self.pid}'

def create_data():
    Base.metadata.drop_all(engine)
    Base.metadata.create_all(engine)
    city1 = City(id=1, name='河北省', pid=0)
    city2 = City(id=10, name='石家庄', pid=1)
    city3 = City(id=11, name='天津市', pid=1)
    city4 = City(id=2, name='广东省', pid=0)
    city5 = City(id=21, name='深圳市', pid=2)
    city6 = City(id=22, name='广州市', pid=2)

    with Session() as ses:
        ses.add_all([city1,city2,city3,city4,city5,city6])
        ses.commit()

def query_data():
    c = aliased(City)
    with Session() as ses:
        rs = ses.query(City,c.name,c.id).join(c,City.id==c.pid).all()
        print(rs)

if __name__ == '__main__':
    # create_data()
    query_data()
